﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using System.Collections;

namespace Diary.BAL
{
    public class DBController
    {
        static DBController _DefaultInstance = null;
        public static DBController Default
        {
            get
            {
                if (_DefaultInstance == null)
                {
                    _DefaultInstance = new DBController();
                }
                return _DefaultInstance;
            }
        }

        SqliteConnection _conn;
        public SqliteException LastException;


        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public bool connectToDatabase(string filepath)
        {
            try
            {
                if (!System.IO.File.Exists(filepath)) return false;

                string connstring = string.Format("Data Source={0};", filepath);
                _conn = new SqliteConnection(connstring);
                _conn.Open();
                return _conn.State == System.Data.ConnectionState.Open;
            }
            catch (SqliteException ex)
            {
                LastException = ex;
                return false;
            }
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void disconnect()
        {
            if (_conn.State == System.Data.ConnectionState.Open)
            {
                _conn.Close();
            }
        }

        /// <summary>
        /// 执行sql语句无返回结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool executeNoReturn(string sql)
        {
            try
            {
                SqliteCommand command = new SqliteCommand(sql, _conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqliteException ex)
            {
                LastException = ex;
                return false;
            }
        }

        /// <summary>
        /// 执行sql语句返回结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object executeHasReturn(string sql)
        {
            try
            {
                if (_conn == null) return null;

                SqliteCommand command = new SqliteCommand(sql, _conn);
                return command.ExecuteScalar();
            }
            catch (SqliteException ex)
            {
                LastException = ex;
                return null;
            }
        }

        /// <summary>
        /// 执行sql语句返回最后的ID，一般用于一条新建记录后返回新记录的ID
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int executeReturnLastId(string sql)
        {
            if (!sql.Contains("last_insert_rowid"))
            {
                sql += "; SELECT last_insert_rowid();";
            }

            int id = 0;
            object ret = executeHasReturn(sql);
            if (ret != null)
            {
                if (int.TryParse(ret.ToString(), out id))
                {
                }
            }
            return id;
        }

        /// <summary>
        /// 是否存在记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool hasRecord(string sql)
        {
            try
            {
                SqliteCommand command = new SqliteCommand(sql, _conn);
                SqliteDataReader reader = command.ExecuteReader();
                return reader.Read();
            }
            catch (SqliteException ex)
            {
                LastException = ex;
                return false;
            }
        }

        /// <summary>
        /// 将reader数据变成hashtable
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        Hashtable _reader2hash(SqliteDataReader reader)
        {
            Hashtable values = new Hashtable();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                object value = reader[i];
                string name = reader.GetName(i);
                values.Add(name, value);
            }
            return values;
        }

        /// <summary>
        /// 查询sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<Hashtable> query(string sql)
        {
            List<Hashtable> results = new List<Hashtable>();
            try
            {
                if (_conn == null) return results;
                SqliteCommand command = new SqliteCommand(sql, _conn);
                SqliteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Hashtable values = _reader2hash(reader);
                    results.Add(values);
                }

                return results;
            }
            catch (SqliteException ex)
            {
                LastException = ex;
                return results;
            }
        }

        /// <summary>
        /// 查询sql语句，返回第1条记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Hashtable query_first(string sql)
        {
            Hashtable result = new Hashtable();
            try
            {
                if (!sql.Trim().ToLower().EndsWith("limit 1"))
                {
                    sql += " limit 1";
                }
                SqliteCommand command = new SqliteCommand(sql, _conn);
                SqliteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result = _reader2hash(reader);
                }

                if (result.Count == 0) return null;

                return result;
            }
            catch (SqliteException ex)
            {
                LastException = ex;
                return null;
            }
        }


    }
}
